A query statement can be compiled into a query plan consisting of query operators. A query operator can be executed in many different ways, for example full table scans, index scans, nested loop joins, hash joins, and others. A query optimizer is a component of a database management system that attempts to determine the most efficient way to execute a query. The query optimizer determines the most efficient way to execute a SQL statement after considering many factors related to the objects referenced and the conditions specified in the query. The determination is a useful step in the processing of any query statement and can greatly affect execution time.
The query optimizer compares the available query plans for a target input query and estimates which of plan will be the most efficient in practice. One type of query optimizer operates on a cost basis and assigns an estimated cost to each possible query plan, for example selecting the plan with the smallest cost. Costs can be used to estimate the runtime cost of evaluating the query in terms of factors such as the number of I/O operations required, processor load requirements, and other factors which can be set forth in a data structure called a data dictionary which stores statistics used by the query optimizer. The set of available query plans that are examined is formed by examining the possible access paths, such as index scan and sequential scan, and join algorithms including sort-merge join, hash join, nested loops, and others. A search space can become very large according to complexity of the query.
Performance of a database system during processing of a query depends on the ability of a query optimizer to select an appropriate plan for executing the query under an expected set of conditions (for example, cardinality estimates, resource availability assumptions), and the ability of an executor to process the query using the selected plan under actual runtime conditions.
Some approaches to managing database system performance focus on the query optimizer's ability to select an appropriate plan. Even techniques that consider the disparity between expected and actual runtime conditions focus on assisting the query optimizer to pick the best plan with regard to such disparity.